﻿CREATE PROCEDURE admin.s_HtmlTable AS
DECLARE @SQL nvarchar(max)
SELECT @SQL =CONVERT(nvarchar(max), (
select 2 [@cellpadding]
      ,2 [@cellspacing]
      ,1 [@border]
--This returns the header
       ,(select th
            from (select 'Database Table' th 
               union all
                  select 'Entity Count' 
               union all
                  select 'Total Rows') d --name of this alias is irrelevant but you have to have one
          for xml path(''),type) tr --tr here defines that the header row will be a tr element
--This returns the rows in the table
        ,(select (select 1 as'@colspan',dbtable   for xml path('td'),type),
                 (select entities  for xml path('td'),type),
                 (select rows  for xml path('td'),type)
            from (--We have to use a derived table because we are grouping
                  select dbtable  = object_name( object_id ),
                         entities = count( distinct name ),
                         rows     = count( * )
                    from sys.columns
                group by object_name( object_id )) data --name of this alias is irrelevant but you have to have one
           for xml path ('tr'),type) --path('tr') turns each row into a tr element
 for xml path('table'), type


))
PRINT @SQL